package com.example.stroebackstage.dao;
import com.example.stroebackstage.entity.Order;
import com.example.stroebackstage.entity.Order;
import com.example.stroebackstage.utils.LinkMySQL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Objects;

public class OrderDao {
    //    增弃用
    public  static boolean insertOrder(Order order){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int numb = 0;
        try {
            conn = LinkMySQL.getConnection();
            String sql = "insert into orders(customerEmail,commodityId,commodityNumber,consigneeName,consigneeAddress,consigneePAC,consigneePhone,consigneeFax,cardType,carName,cardNumber,billAddress,closingDate,taxRate,freight)" +
                    " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            pstmt = Objects.requireNonNull(conn).prepareStatement(sql);
            pstmt.setString(1,order.getCustomerEmail());
            pstmt.setInt(2,order.getCommodityId());
            pstmt.setInt(3,order.getCommodityNumber());
            pstmt.setString(4,order.getConsigneeName());
            pstmt.setString(5,order.getConsigneeAddress());
            pstmt.setString(6,order.getConsigneePAC());
            pstmt.setString(7,order.getConsigneePhone());
            pstmt.setString(8,order.getConsigneeFax());
            pstmt.setString(9,order.getCardType());
            pstmt.setString(10,order.getCarName());
            pstmt.setString(11,order.getCardNumber());
            pstmt.setString(12,order.getBillAddress());
            pstmt.setString(13,order.getClosingDate());
            pstmt.setDouble(14,order.getTaxRate());
            pstmt.setDouble(15,order.getFreight());
            numb = pstmt.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(pstmt,conn);
        }
        return numb > 0;
    }

    //    删
    public static boolean delOrder(int id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int numb = 0;
        try {
            conn = LinkMySQL.getConnection();
            String sql =
                    "delete from orders where order_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            numb = pstmt.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(pstmt,conn);
        }
        return numb>0;
    }
    //    改
//    弃用
//    public static boolean updateOrder(Order order){
//        Connection conn = null;
//        PreparedStatement pstmt = null;
//        int numb = 0;
//        try {
//            conn = LinkMySQL.getConnection();
//            String sql =
//                    "update orders set customerEmail=?,commodityId=?,commodityNumber=?,consigneeName=?," +
//                            "consigneeAddress=?,consigneePAC=?,consigneePhone=?,consigneeFax=?,cardType=?," +
//                            "carName=?,cardNumber=?,billAddress=?,closingDate=?,taxRate=?,freight=? where order_id=?" ;
//            pstmt = conn.prepareStatement(sql);
//            pstmt.setString(1,order.getCustomerEmail());
//            pstmt.setInt(2,order.getCommodityId());
//            pstmt.setInt(3,order.getCommodityNumber());
//            pstmt.setString(4,order.getConsigneeName());
//            pstmt.setString(5,order.getConsigneeAddress());
//            pstmt.setString(6,order.getConsigneePAC());
//            pstmt.setString(7,order.getConsigneePhone());
//            pstmt.setString(8,order.getConsigneeFax());
//            pstmt.setString(9,order.getCardType());
//            pstmt.setString(10,order.getCarName());
//            pstmt.setString(11,order.getCardNumber());
//            pstmt.setString(12,order.getBillAddress());
//            pstmt.setString(13,order.getClosingDate());
//            pstmt.setDouble(14,order.getTaxRate());
//            pstmt.setDouble(15,order.getFreight());
//            pstmt.setInt(16,order.getOrderId());
//            numb = pstmt.executeUpdate();
//        }catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            LinkMySQL.release(pstmt,conn);
//        }
//        return numb>0;
//    }
    public static boolean updateOrder(Order order){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int numb = 0;
        try {
            conn = LinkMySQL.getConnection();
            String sql =
                    "update orders set consigneeName=?,consigneeAddress=?,consigneePAC=?,consigneePhone=? where order_id=?" ;
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,order.getConsigneeName());
            pstmt.setString(2,order.getConsigneeAddress());
            pstmt.setString(3,order.getConsigneePAC());
            pstmt.setString(4,order.getConsigneePhone());
            pstmt.setInt(5,order.getOrderId());
            numb = pstmt.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(pstmt,conn);
        }
        return numb>0;
    }
    //    查
    public static ArrayList<Order> selectOrder(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Order> orderArrayList = new ArrayList<>();
        try {
            conn = LinkMySQL.getConnection();
            String sql = "select * from orders ";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
               Order order = new Order(rs.getInt("order_id"),rs.getString("customerEmail"),
                       rs.getInt("commodityId"),rs.getInt("commodityNumber"),
                       rs.getString("consigneeName"), rs.getString("consigneeAddress"),
                       rs.getString("consigneePAC"), rs.getString("consigneePhone"),
                       rs.getString("consigneeFax"),rs.getString("cardType"),
                       rs.getString("carName"),rs.getString("cardNumber"),
                       rs.getString("billAddress"), rs.getString("closingDate"),
                       rs.getDouble("taxRate"), rs.getDouble("freight"),rs.getDouble("commodityPrice"));
               orderArrayList.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            LinkMySQL.release(rs,pstmt,conn);
        }
        return orderArrayList;
    }
    public static ArrayList<Order> selectOrderByCommodityId(int id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Order> orderArrayList = new ArrayList<>();
        try {
            conn = LinkMySQL.getConnection();
            String sql = "select * from orders where commodityId = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Order order = new Order(rs.getInt("order_id"),rs.getString("customerEmail"),
                        rs.getInt("commodityId"),rs.getInt("commodityNumber"),
                        rs.getString("consigneeName"), rs.getString("consigneeAddress"),
                        rs.getString("consigneePAC"), rs.getString("consigneePhone"),
                        rs.getString("consigneeFax"),rs.getString("cardType"),
                        rs.getString("carName"),rs.getString("cardNumber"),
                        rs.getString("billAddress"), rs.getString("closingDate"),
                        rs.getDouble("taxRate"), rs.getDouble("freight"),rs.getDouble("commodityPrice"));
                orderArrayList.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            LinkMySQL.release(rs,pstmt,conn);
        }
        return orderArrayList;
    }
    public static ArrayList<Order> selectOrderById(int id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Order> orderArrayList = new ArrayList<>();
        try {
            conn = LinkMySQL.getConnection();
            String sql = "select * from orders where order_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Order order = new Order(rs.getInt("order_id"),rs.getString("customerEmail"),
                        rs.getInt("commodityId"),rs.getInt("commodityNumber"),
                        rs.getString("consigneeName"), rs.getString("consigneeAddress"),
                        rs.getString("consigneePAC"), rs.getString("consigneePhone"),
                        rs.getString("consigneeFax"),rs.getString("cardType"),
                        rs.getString("carName"),rs.getString("cardNumber"),
                        rs.getString("billAddress"), rs.getString("closingDate"),
                        rs.getDouble("taxRate"), rs.getDouble("freight"),rs.getDouble("commodityPrice"));
                orderArrayList.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            LinkMySQL.release(rs,pstmt,conn);
        }
        return orderArrayList;
    }
    public static ArrayList<Order> selectOrderByCustomerEmail(String email){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Order> orderArrayList = new ArrayList<>();
        try {
            conn = LinkMySQL.getConnection();
            String sql = "select * from orders where customerEmail like ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"%"+email+"%");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Order order = new Order(rs.getInt("order_id"),rs.getString("customerEmail"),
                        rs.getInt("commodityId"),rs.getInt("commodityNumber"),
                        rs.getString("consigneeName"), rs.getString("consigneeAddress"),
                        rs.getString("consigneePAC"), rs.getString("consigneePhone"),
                        rs.getString("consigneeFax"),rs.getString("cardType"),
                        rs.getString("carName"),rs.getString("cardNumber"),
                        rs.getString("billAddress"), rs.getString("closingDate"),
                        rs.getDouble("taxRate"), rs.getDouble("freight"),rs.getDouble("commodityPrice"));
                orderArrayList.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            LinkMySQL.release(rs,pstmt,conn);
        }
        return orderArrayList;
    }
}
